package com.rc.saas.tenant.common.utils;

import com.rc.saas.tenant.common.annotation.Excel;
import com.rc.saas.tenant.common.annotation.Log;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * @author wangzhengwei
 * @create 2021/7/22
 */
public class CommonExcel {
    private static final Integer SHEET_MAX_ROW=10000;

    private static final Integer SHEET_ROW_ACCESS=100;//内存中缓存记录行数

    private ByteArrayOutputStream os = null;

    public static void downLoadWorkBook(HttpServletResponse response, List<?> result, String sheetName, String fileName) {

        try{

            long curr_time=System.currentTimeMillis();

            //如果为空返回无数据!
            if (result == null || result.isEmpty()) {
                returnVoid(response,result, sheetName,fileName);
                return ;
            }

            //生成的sheet文件数量=sheetNum+1

            //int sheetNum = result.size()/SHEET_MAX_ROW;
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            SXSSFWorkbook wb = new SXSSFWorkbook(SHEET_ROW_ACCESS);

            SXSSFSheet sh = wb.createSheet(sheetName);
            // 4.创建单元格，设置值表头，设置表头居中
            CellStyle style = wb.createCellStyle();
            // 居中格式
            //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setAlignment(HorizontalAlignment.CENTER);

            //每个SHEET有10000ROW
            Class<?> resultClass = result.get(0).getClass();
            Field[] fields = resultClass.getDeclaredFields();
            Method[] methods = new Method[fields.length];
            String[] contents = new String[fields.length];
            Excel[] excels = new Excel[fields.length];
            Excel excel = null;
            for (int i = 0; i < fields.length; i++) {
                excel = fields[i].getAnnotation(Excel.class);
                contents[i] = excel.title();
                String methodName = "get" + fields[i].getName().substring(0, 1).toUpperCase() + fields[i].getName().substring(1);
                Method method = resultClass.getMethod(methodName, null);
                methods[i] = method;
                excels[i] = excel;
            }
            // 设置表头
            createCell(sh.createRow(0), style, contents);
            //rownum=0 做为表头
            for(int rownum = 1; rownum <= result.size(); rownum++) {

                //SXSSFRow row = sh.createRow(rownum);
                //setRowValue(result, sh, row, style);
                for (int k = 0; k < methods.length; k++) {

                    try {
                        Object obj = methods[k].invoke(result.get(rownum-1), null);
                        if (obj != null) {
                            contents[k] = obj.toString();
                        } else {
                            contents[k] = "";
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if (excels[k].index().length > 0) {
                        contents[k] = getExcelValue(excels[k], contents[k]);
                    }
                }

                SXSSFRow row = sh.createRow(rownum);
                // 给每行一一对应的添加数据
                createCell(row, style, contents);
                //每当行数达到设置的值就刷新数据到硬盘,以清理内存

                if(rownum%SHEET_ROW_ACCESS==0){

                    ((SXSSFSheet)sh).flushRows();

                }

            }


            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1") + ".xls");// iso-8859-1
            ServletOutputStream out = response.getOutputStream();
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try {
                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);
                byte[] buff = new byte[2048];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            }
            /*写数据到文件中*/

            //FileOutputStream os = new FileOutputStream("E:/biggrid.xlsx");

            //wb.write(os);

            //os.close();



            /*计算耗时*/

            System.out.println("耗时:"+(System.currentTimeMillis()-curr_time));

        } catch(Exception e) {

            e.printStackTrace();

        }

    }



    private static void setRowValue(List<?> result, SXSSFSheet sheet, SXSSFRow row, CellStyle style) throws Exception {
        Class<?> resultClass = result.get(0).getClass();
        Field[] fields = resultClass.getDeclaredFields();
        Method[] methods = new Method[fields.length];
        String[] contents = new String[fields.length];
        Excel[] excels = new Excel[fields.length];
        Excel excel = null;
        for (int i = 0; i < fields.length; i++) {
            excel = fields[i].getAnnotation(Excel.class);
            contents[i] = excel.title();
            String methodName = "get" + fields[i].getName().substring(0, 1).toUpperCase() + fields[i].getName().substring(1);
            Method method = resultClass.getMethod(methodName, null);
            methods[i] = method;
            excels[i] = excel;
        }
        // 设置表头
        createCell(row, style, contents);

        for (int j = 0; j < result.size(); j++) {
            for (int k = 0; k < methods.length; k++) {

                try {
                    Object obj = methods[k].invoke(result.get(j), null);
                    if (obj != null) {
                        contents[k] = obj.toString();
                    } else {
                        contents[k] = "";
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }

                if (excels[k].index().length > 0) {
                    contents[k] = getExcelValue(excels[k], contents[k]);
                }
            }
            row = sheet.createRow(j + 1);
            // 给每行一一对应的添加数据
            createCell(row, style, contents);

        }
    }


    private static void setRowValue0(List<?> result, SXSSFSheet sheet, SXSSFRow row, CellStyle style) throws Exception {
        Class<?> resultClass = result.get(0).getClass();
        Field[] fields = resultClass.getDeclaredFields();
        Method[] methods = new Method[fields.length];
        String[] contents = new String[fields.length];
        Excel[] excels = new Excel[fields.length];
        Excel excel = null;
        for (int i = 0; i < fields.length; i++) {
            excel = fields[i].getAnnotation(Excel.class);
            contents[i] = excel.title();
            String methodName = "get" + fields[i].getName().substring(0, 1).toUpperCase() + fields[i].getName().substring(1);
            Method method = resultClass.getMethod(methodName, null);
            methods[i] = method;
            excels[i] = excel;
        }
        // 设置表头
        createCell(row, style, contents);

        for (int j = 0; j < result.size(); j++) {
            for (int k = 0; k < methods.length; k++) {

                try {
                    Object obj = methods[k].invoke(result.get(j), null);
                    if (obj != null) {
                        contents[k] = obj.toString();
                    } else {
                        contents[k] = "";
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }

                if (excels[k].index().length > 0) {
                    contents[k] = getExcelValue(excels[k], contents[k]);
                }
            }
            row = sheet.createRow(j + 1);
            // 给每行一一对应的添加数据
            createCell(row, style, contents);

        }
    }

    private static void createCell(SXSSFRow row, CellStyle style, String[] contents) {
        for (int i = 0; i < contents.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellValue(contents[i]);
            cell.setCellStyle(style);
        }
    }

    /**
     * 通过注解获取对象的修改前后内容
     *
     * @param beforeObj
     * @param afterObj
     * @return
     * @throws Exception
     */
    /*public static OperatorLog getLogContent(Object beforeObj, Object afterObj, McUser user) throws Exception {
        StringBuffer beforeContent = new StringBuffer(); // 修改前内容
        StringBuffer afterContent = new StringBuffer(); // 修改后内容

        Class objClass = beforeObj.getClass();
        Field[] fields = objClass.getDeclaredFields();
        for (Field field : fields) {
            // 获取注解
            Log log = field.getAnnotation(Log.class);
            if (log == null) {
                continue;
            }
            // 获取get方法
            String methodName = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
            Method method = objClass.getMethod(methodName, null);
            Object afterValue = method.invoke(afterObj, null);
            if (afterValue == null) {
                continue;
            }
            Object beforeValue = method.invoke(beforeObj, null);
            getContent(beforeContent, log, beforeValue);
            getContent(afterContent, log, afterValue);

        }
        Method idMethod = objClass.getMethod("getId", null);
        Long targetId = (Long) idMethod.invoke(afterObj, null);
        Log target = (Log) objClass.getAnnotation(Log.class);
        return new OperatorLog(user.getId(), user.getUsername(), target.title(), targetId, WebConstant.LOG_ACTION_EDIT,
                beforeContent.toString(), afterContent.toString(), new Date());
    }*/

    private static void getContent(StringBuffer content, Log log, Object value) {
        if (content.length() > 0) {
            content.append(",");
        }
        content.append(log.title()).append(":");
        if (log.index().length > 0 && log.value().length > 0) {
            if (value != null) {
                value = getLogValue(log, value.toString());
            }
        }
        content.append(value);
    }

    private static String getLogValue(Log log, String index) {
        for (int i = 0; i < log.index().length; i++) {
            if (log.index()[i].equals(index)) {
                return log.value()[i];
            }
        }
        return null;
    }

    private static String getExcelValue(Excel excel, String index) {
        for (int i = 0; i < excel.index().length; i++) {
            if (excel.index()[i].equals(index)) {
                return excel.value()[i];
            }
        }
        return null;
    }

    // 生成指定长度的密码
    public static String createPassWord(int len) {
        // 得到0.0到1.0之间的数字，并扩大100000倍
        double temp = Math.random() * 100000;
        // 如果数据等于100000，则减少1
        if (temp >= 100000) {
            temp = 99999;
        }
        int random = (int) Math.ceil(temp);
        return createPassWord(random, len);
    }

    public static String createPassWord(int random, int len) {
        Random rd = new Random(random);
        final int maxNum = 62;
        StringBuffer sb = new StringBuffer();
        int rdGet;// 取得随机数
        char[] str = { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v',
                'w', 'x', 'y', 'z', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };

        int count = 0;
        while (count < len) {
            rdGet = Math.abs(rd.nextInt(maxNum));// 生成的数最大为62-1
            if (rdGet >= 0 && rdGet < str.length) {
                sb.append(str[rdGet]);
                count++;
            }
        }
        return sb.toString();
    }

    private static void returnVoid(HttpServletResponse response, List<?> result, String sheetName, String fileName) throws IOException {

        SXSSFWorkbook wb = new SXSSFWorkbook();
        // 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        SXSSFSheet sheet = wb.createSheet(sheetName);
        // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short
        SXSSFRow row = sheet.createRow((int) 0);
        // 4.创建单元格，设置值表头，设置表头居中
        CellStyle style = wb.createCellStyle();
        SXSSFCell cell = row.createCell(0);
        cell.setCellValue("无数据!");
        cell.setCellStyle(style);
        // 居中格式
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1") + ".xls");// iso-8859-1
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }
}
